from flask import Flask, request, render_template, redirect, url_for, flash
from flask_sqlalchemy import SQLAlchemy
import pymysql
import os

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://root:123456@localhost/sct'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
app.config['SECRET_KEY'] ="XXX"
db = SQLAlchemy(app)


class Student(db.Model):
    sno = db.Column(db.String(20), primary_key=True)
    sname = db.Column(db.String(20), nullable=False)
    ssex = db.Column(db.String(20), nullable=False)
    sage = db.Column(db.Integer, nullable=False)
    dno = db.Column(db.String(20), nullable=False)
    sclass = db.Column(db.String(20))
    address = db.Column(db.String(255))

    def __repr__(self):
        return f'<Student {self.sname}>'


@app.route('/')
def index():
    students = Student.query.all()
    return render_template('index.html', students=students)


@app.route('/add', methods=['GET', 'POST'])
def add_student():
    if request.method == 'POST':
        try:
            sno = request.form['sno']
            sname = request.form['sname']
            ssex = request.form['ssex']
            sage = int(request.form['sage'])  # 确保年龄是整数
            dno = request.form['dno']
            sclass = request.form['sclass']
            address = request.form['address']

            new_student = Student(sno=sno, sname=sname, ssex=ssex, sage=sage, dno=dno, sclass=sclass, address=address)
            db.session.add(new_student)
            db.session.commit()
            flash('Student added successfully!')
            return redirect(url_for('index'))
        except Exception as e:
            db.session.rollback()  # 回滚事务
            flash('Error adding student: ' + str(e))
            return render_template('add.html')
    return render_template('add.html')


@app.route('/edit/<sno>', methods=['GET', 'POST'])
def edit_student(sno):
    student = Student.query.filter_by(sno=sno).first()
    if request.method == 'POST':
        student.sname = request.form['sname']
        student.ssex = request.form['ssex']
        student.sage = request.form['sage']
        student.dno = request.form['dno']
        student.sclass = request.form['sclass']
        student.address = request.form['address']
        db.session.commit()
        flash('Student updated successfully!')
        return redirect(url_for('index'))
    return render_template('edit.html', student=student)


@app.route('/delete/<sno>', methods=['POST'])
def delete_student(sno):
    student = Student.query.filter_by(sno=sno).first()
    db.session.delete(student)
    db.session.commit()
    flash('Student deleted successfully!')
    return redirect(url_for('index'))

with app.app_context():  # 添加应用程序上下文
    db.create_all()

if __name__ == '__main__':
    app.run(debug=True)